﻿using CreateCodeByDatabase.Model.MS_XTPT;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CreateCodeByDatabase
{
    /// <summary>
    /// 供应商协同平台【棉纱】Excel解析
    /// </summary>
    public class ExcelHelper_MS_XTPT
    {
        public static Dictionary<string,List<TableComment>> GetFieldComment(string filePath)
        {
            if (string.IsNullOrWhiteSpace(filePath))
                return null;
            if (!File.Exists(filePath))
                throw new Exception("指定Excel数据字典文件不存在");
            Dictionary<string, List<TableComment>> resultDic = new Dictionary<string, List<TableComment>>();
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite, bufferSize: 4096))
            {
                IWorkbook workbook = null;
                string extension = Path.GetExtension(filePath).ToUpper();
                if (extension == ".XLSX") // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (extension == ".XLS")  // 2003版本
                    workbook = new HSSFWorkbook(fs);

                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    if (workbook.IsSheetHidden(i))
                        continue;
                    ISheet sheet = workbook.GetSheetAt(i);
                    if (sheet == null)
                        continue;
                    IRow row = sheet.GetRow(2);
                    if (row == null)
                        continue;
                    ICell cell = row.GetCell(1);
                    if (cell == null)
                        continue;
                    if( cell.CellType != CellType.String || cell.StringCellValue != "字段中文名")
                        continue;
                    row = sheet.GetRow(1);
                    cell = row.GetCell(1);
                    string tableName = cell.StringCellValue;
                    if(tableName.IndexOf("(") > 0)
                        tableName = tableName.Substring(tableName.IndexOf("(") + 1).TrimEnd(')').TrimEnd('）');
                    else if (tableName.IndexOf("（") > 0)
                        tableName = tableName.Substring(tableName.IndexOf("（") + 1).TrimEnd(')').TrimEnd('）');
                    else
                        throw new Exception("表明拼装规则变动");
                    resultDic.Add(tableName,new List<TableComment>());

                    for (int j = 3; j <= sheet.LastRowNum; j++)
                    {
                        row = sheet.GetRow(j);
                        if (row == null)
                            continue;
                        cell = row.GetCell(1);
                        if (cell == null)
                            continue;
                        if (string.IsNullOrWhiteSpace(cell.StringCellValue))
                            continue;
                        TableComment entity = new TableComment();
                        entity.FieldName = cell.StringCellValue;
                        cell = row.GetCell(2);
                        entity.FieldCode = cell.StringCellValue;
                        cell = row.GetCell(4);
                        entity.Comment = cell.StringCellValue;
                        resultDic[tableName].Add(entity);
                    }
                }
            }
            return resultDic;
        }
    }
}
